#coding:utf-8
from django.shortcuts import render,HttpResponse,redirect
# Create your views here.

from .models import *
from .api import *
from django.views.decorators.csrf import csrf_exempt
import json
from django.core.serializers import serialize

import MySQLdb
import time,datetime
import urlparse
import urllib

from .upload import image_upload

#from .erp import *

from functools import wraps

from .forms import *

from django.contrib.auth import logout, login, authenticate
from django.contrib.auth.hashers import make_password

from django.core.paginator import Paginator, InvalidPage, EmptyPage, PageNotAnInteger

from django.db.models import Q

import csv
import time
import xlrd


from django.utils.timezone import now, timedelta

from django.conf import settings
from .logs import iterbrowse
import os

password = settings.DATABASES["default"]["PASSWORD"]
dbname = settings.DATABASES["default"]["NAME"]
dbuser = settings.DATABASES["default"]["USER"]
host = settings.DATABASES["default"]["HOST"]
port = settings.DATABASES["default"]["PORT"]

if host == '':
    host = 'localhost'

if port == '':
    port = 3306

def linkDB(dbname=dbname):
    conn=MySQLdb.connect(host=host,user=dbuser,passwd=password,db=dbname,port=port,charset='utf8')
    return conn


def global_data(request):

    exchangeRate = '6.9497'

    if request.user.is_authenticated():
        user = request.user
        notice_mark = NoticeMark.objects.filter(user=request.user,is_read=True)
        readNoticeIds = notice_mark.values('notice_id')
        unread_notice = Notice.objects.exclude(id__in=readNoticeIds).order_by('-send_time')
        unread_num = unread_notice.count()
        unread_notice = unread_notice[:3]

        message_mark = MessageMark.objects.filter(user=request.user,is_read=True)
        readMailIds = message_mark.values('message_id')
        unread_messages = Message.objects.filter(receiver=request.user).order_by('-send_time')
        unread_messages = unread_messages.exclude(id__in=readMailIds)
        unread_mail = unread_messages.count()
        unread_messages = unread_messages[:3]

    return locals()


def home(request):
    #return redirect('dashboard')
    return render(request,'aside/home.html',locals())


def buy(request):
    #return redirect('dashboard')
    return render(request,'aside/buy.html',locals())


def mytask(request):
    avtive_nav_menu = 'mytask'
    return render(request,'ucenter/mytask.html',locals())


def profile(request):
    avtive_nav_menu = 'ucenter'
    if not request.user.is_authenticated():
        return redirect('signin')
    user = request.user
    try:
        user_profile = UserProfile.objects.get(user=user)
    except Exception as e:
        print(e)
        user_profile = UserProfile.objects.create(user=user,name=user.username)


    if request.method == 'POST':
        name = request.POST.get('name')
        tel = request.POST.get('tel')
        email = request.POST.get('email')

        if name:
            user_profile.name = name
            user_profile.save()
        if tel:
            user_profile.tel = tel
            user_profile.save()
        if email:
            user_profile.email = email
            user_profile.save()

        avatar = request.FILES.get('avatar',None)
        if avatar:
            result = image_upload(avatar,'avatar')
            if result["error"] == 0:
                url = result["url"]
            else:
                return render(request, 'aside/error.html', {'msg': result["message"]})
            url = url.replace('/uploads/','')
            user_profile.avatar = url
            user_profile.save()

        dxm_username = request.POST.get('dxm_username')
        dxm_password = request.POST.get('dxm_password')

        if dxm_username:
            user_profile.dxm_username = dxm_username
            user_profile.save()
        if dxm_password:
            user_profile.dxm_password = dxm_password
            user_profile.save()

    #return render(request, 'ucenter/profile.html',locals())
    return render(request, 'velonic/profile.html',locals())


def dashboard(request):
    if not request.user.is_authenticated():
        return redirect('signin')
    user = request.user

    try:
        user_profile = UserProfile.objects.get(user=user)
    except:
        return redirect('profile')

    productNum = Products.objects.all().count()
    storeNum = Products.objects.values('store_id').distinct().count()
    transanctionNum = Transaction.objects.count()

    SearchWords.objects.filter(update_time__lte=now().date() + timedelta(days=-0)).update(status=0)
    wordList = SearchWords.objects.all().order_by('-update_time')

    category_list = Category.objects.all()

    top_products = Products.objects.order_by('-seven_growth_rates')[:4]
    last_upadte_products = Products.objects.filter(trans_update_date__lte=now().date() + timedelta(days=1)).order_by('-add_date')[:4]
    new_products = Products.objects.filter(is_new=True).order_by('-add_date')[:4]

    return render(request, 'zircos/dashboard.html',locals())


def analysis(request):
    if not request.user.is_authenticated():
        #return redirect('signin')
        return redirect('/ucenter/login/'+"?from="+request.get_full_path())
    user = request.user

    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        msg = "未授权用户"
        return render(request, 'aside/error.html', locals())

    try:
        user_profile = UserProfile.objects.get(user=user)
    except:
        return redirect('profile')

    productNum = Products.objects.all().count()
    storeNum = Products.objects.values('store_id').distinct().count()
    transanctionNum = Transaction.objects.count()

    return render(request, 'velonic/analysis.html',locals())


def ucenter(request):
    avtive_nav_menu = 'ucenter'
    if not request.user.is_authenticated():
        return redirect('signin')
    user = request.user

    if user.groups.filter(name='超级管理').count() > 0 :
        return redirect('system:index')
    elif user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        msg = "未授权用户"
        return render(request, 'aside/error.html', locals())

    user_profile = UserProfile.objects.get(user=user)

    if not user_profile:
        user_profile = UserProfile.objects.create(user=user,name=user.username)
        return redirect('profile')

    keyword = request.POST.get('keyword')
    url = request.POST.get('url')
    lotkeywords = request.POST.get('lotkeywords')
    if keyword and keyword.strip():
        keyword = keyword.strip()
        try:
            kw = SearchWords.objects.create(keyword=keyword)
            kw.user.add(user)
            kw.save()
        except Exception as e:
            kw = SearchWords.objects.get(keyword=keyword)
            kw.user.add(user)
            kw.save()
            '''
            plist = Products.objects.filter(keyword=kw)
            for p in plist:
                p.user.add(user)
            '''

            #msg = "关键词已经存在"
            #return render(request, 'velonic/error.html',locals())

    if url and url.strip():
        result = urlparse.urlparse(url)
        if result.netloc != 'www.aliexpress.com':
            return render(request, 'aside/error.html', {'msg': '不是速卖通的网址！'})
        else:
            paramas = urlparse.parse_qs(result.query,True)
            if not paramas.has_key('SearchText'):
                return render(request, 'aside/error.html', {'msg': 'URL错误，无解析法网址的关键词！'})
            else:
                SearchText = str(paramas['SearchText'][0])
                #print(SearchText)
                try:
                    kw = SearchWords.objects.create(keyword=SearchText,url=url.strip())
                    kw.user.add(user)
                    kw.save()
                except Exception as e:
                    kw = SearchWords.objects.get(keyword=keyword)
                    kw.user.add(user)
                    kw.save()
                    '''
                    plist = Products.objects.filter(keyword=kw)
                    for p in plist:
                        p.user.add(user)
                    '''
                    #msg = "关键词已经存在"
                    #return render(request, 'velonic/error.html',locals())

    if lotkeywords and lotkeywords.strip():
        words = lotkeywords.splitlines()
        for wd in words:
            if wd.strip():
                try:
                    sw = SearchWords.objects.create(keyword=wd.strip())
                    sw.user.add(user)
                    sw.save()
                except Exception as e:
                    kw = SearchWords.objects.get(keyword=wd.strip())
                    kw.user.add(user)
                    kw.save()
                    '''
                    plist = Products.objects.filter(keyword=kw)
                    for p in plist:
                        p.user.add(user)
                    '''
                    print(e)

    #SearchWords.objects.filter(update_time__lte=now().date() + timedelta(days=-0)).update(status=0)
    wordList = SearchWords.objects.filter(user=user).order_by('-update_time')
    productNum = Products.objects.filter(user=user).count()
    storeNum = Products.objects.filter(user=user).values('store_id').distinct().count()

    category_list = Category.objects.all()
    top_products = Products.objects.filter(user=user).order_by('-seven_growth_rates')[:4]
    last_upadte_products = Products.objects.filter(user=user,trans_update_date__lte=now().date() + timedelta(days=1)).order_by('-add_date')[:4]
    new_products = Products.objects.filter(is_new=True).order_by('-add_date')[:4]

    #return render(request, 'velonic/ucenter.html',locals())
    return render(request, 'zircos/ucenter.html',locals())

def message(request):
    avtive_nav_menu = 'ucenter'
    return render(request,'ucenter/message.html',locals())


def favorite(request):
    avtive_nav_menu = 'favorite'
    wd = request.GET.get('word')
    o = request.GET.get('o')
    pg = request.GET.get('page')
    if not request.user.is_authenticated():
        #return redirect('signin')
        return redirect('/ucenter/login/'+"?from="+request.get_full_path())
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        msg = "未授权用户"
        return render(request, 'aside/error.html', locals())

    try:
        user_profile = UserProfile.objects.get(user=user)
    except:
        return redirect('profile')

    Products.objects.filter(product_update_date__lte=now().date() + timedelta(days=-0),user=user).update(status=0)
    Products.objects.filter(trans_update_date__lte=now().date() + timedelta(days=-0),user=user).update(status_trans=0)
    productList = Products.objects.filter(user=user).order_by('-product_update_date')
    #productList = productList.filter(weekly_orders__gt=7)


    productList = getPage(request, productList,16)


    if 'compare_list' in request.COOKIES:
        if request.COOKIES['compare_list']:
            compare_list = request.COOKIES['compare_list']
            compare_list = urllib.unquote(compare_list)
            goods = compare_list.split(',')
            compare_goods = Products.objects.filter(product_id__in=goods)
            idx = [0,1,2,3,4]
            empty_goods = idx[compare_goods.count():]

    #return render(request,'ucenter/favarite.html',locals())
    #return render(request, 'velonic/favarite.html',locals())
    return render(request, 'zircos/favorite.html',locals())


def myProducts(request):
    if not request.user.is_authenticated():
        #return redirect('signin')
        return redirect('/ucenter/login/'+"?from="+request.get_full_path())
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        msg = "未授权用户"
        return render(request, 'aside/error.html', locals())

    mystore = Store.objects.filter(manage_user=user)


    competitor = Competitor.objects.filter(user=user).values('my_product')

    storeIds = mystore.values('store_id')
    productList = Products.objects.filter(store_id__in=storeIds,id__in=competitor)

    productList = getPage(request, productList,30)

    if 'compare_list' in request.COOKIES:
        if request.COOKIES['compare_list']:
            compare_list = request.COOKIES['compare_list']
            compare_list = urllib.unquote(compare_list)
            goods = compare_list.split(',')
            compare_goods = Products.objects.filter(product_id__in=goods)
            idx = [0,1,2,3,4]
            empty_goods = idx[compare_goods.count():]

    return render(request, 'velonic/myProduct.html',locals())


def task_add(request):
    return render(request,'ucenter/favarite.html',locals())


def index(request):
    return redirect('dashboard')
    #return render(request,'ucenter.html',locals())


def search(request):
    #productList = Products.objects.exclude(weekly_orders=None)
    productList = Products.objects.all()

    user = request.user
    wordrank = SearchWords.objects.filter(user=user).order_by('-product_num')

    key = request.GET.get('key')



    if key:
        keyword = SearchWords.objects.filter(keyword__contains=key)[:3]
        #print(keyword)
        if keyword:
            if len(keyword) == 1:
                productList = productList.filter(keyword=keyword[0])
            elif len(keyword) == 2:
                productList = productList.filter(Q(keyword=keyword[0])|Q(keyword=keyword[1]))
            elif len(keyword) == 3:
                productList = productList.filter(Q(keyword=keyword[0])|Q(keyword=keyword[1])|Q(keyword=keyword[2]))

        wordrank = wordrank.filter(keyword__contains=key)

    wordrank = wordrank[:10]

    wd = request.GET.get('word')
    if wd:
        try:
            word = SearchWords.objects.get(pk=wd)
            productList = productList.filter(keyword=word)
        except:
            pass

    filterOrg7 = request.GET.get('filterOrg7')
    filterWeekOrders = request.GET.get('filterWeekOrders')
    filterOrders = request.GET.get('filterOrders')
    filterVotes = request.GET.get('filterVotes')
    filterPrice = request.GET.get('filterPrice')

    if filterOrg7:
        if filterOrg7 == '1':
            productList = productList.filter(seven_growth_rates__gte=3.0)
        elif filterOrg7 == '2':
            productList = productList.filter(seven_growth_rates__gte=2.0)
        elif filterOrg7 == '3':
            productList = productList.filter(seven_growth_rates__gte=1.0)
        elif filterOrg7 == '4':
            productList = productList.filter(seven_growth_rates__gte=0.8)
        elif filterOrg7 == '5':
            productList = productList.filter(seven_growth_rates__gte=0.5)
        elif filterOrg7 == '6':
            productList = productList.filter(seven_growth_rates__gte=0.3)
        elif filterOrg7 == '7':
            productList = productList.filter(seven_growth_rates__gte=0.2)
        elif filterOrg7 == '8':
            productList = productList.filter(seven_growth_rates__gte=0.1)
        elif filterOrg7 == '9':
            productList = productList.filter(seven_growth_rates__gte=0.05)
        elif filterOrg7 == '10':
            productList = productList.filter(seven_growth_rates__lt=0.05)

    if filterWeekOrders:
        if filterWeekOrders == '1':
            productList = productList.filter(weekly_orders__gte=100)
        elif filterWeekOrders == '2':
            productList = productList.filter(weekly_orders__gte=80)
        elif filterWeekOrders == '3':
            productList = productList.filter(weekly_orders__gte=50)
        elif filterWeekOrders == '4':
            productList = productList.filter(weekly_orders__gte=30)
        elif filterWeekOrders == '5':
            productList = productList.filter(weekly_orders__gte=20)
        elif filterWeekOrders == '6':
            productList = productList.filter(weekly_orders__gte=10)
        elif filterWeekOrders == '7':
            productList = productList.filter(weekly_orders__gte=5)
        elif filterWeekOrders == '8':
            productList = productList.filter(weekly_orders__lt=5)

    if filterOrders:
        if filterOrders == '1':
            productList = productList.filter(orders__gte=3000)
        elif filterOrders == '2':
            productList = productList.filter(orders__gte=2000)
        elif filterOrders == '3':
            productList = productList.filter(orders__gte=1000)
        elif filterOrders == '4':
            productList = productList.filter(orders__gte=500)
        elif filterOrders == '5':
            productList = productList.filter(orders__gte=200)
        elif filterOrders == '6':
            productList = productList.filter(orders__gte=100)
        elif filterOrders == '7':
            productList = productList.filter(orders__gte=50)
        elif filterOrders == '8':
            productList = productList.filter(orders__gte=30)
        elif filterOrders == '9':
            productList = productList.filter(orders__gte=10)
        elif filterOrders == '10':
            productList = productList.filter(orders__lt=10)

    if filterVotes:
        if filterVotes == '1':
            productList = productList.filter(votes__gte=2000)
        elif filterVotes == '2':
            productList = productList.filter(votes__gte=1000)
        elif filterVotes == '3':
            productList = productList.filter(votes__gte=500)
        elif filterVotes == '4':
            productList = productList.filter(votes__gte=300)
        elif filterVotes == '5':
            productList = productList.filter(votes__gte=100)
        elif filterVotes == '6':
            productList = productList.filter(votes__gte=50)
        elif filterVotes == '7':
            productList = productList.filter(votes__gte=20)
        elif filterVotes == '8':
            productList = productList.filter(votes__gte=10)
        elif filterVotes == '9':
            productList = productList.filter(votes__lt=10)


    if filterPrice:
        if filterPrice == '1':
            productList = productList.filter(low_price__gt=100)
        elif filterPrice == '2':
            productList = productList.filter(Q(low_price__gt=50)&Q(low_price__lte=100))
        elif filterPrice == '3':
            productList = productList.filter(Q(low_price__gt=30)&Q(low_price__lte=50))
        elif filterPrice == '4':
            productList = productList.filter(Q(low_price__gt=20)&Q(low_price__lte=30))
        elif filterPrice == '5':
            productList = productList.filter(Q(low_price__gt=10)&Q(low_price__lte=20))
        elif filterPrice == '6':
            productList = productList.filter(Q(low_price__gt=5)&Q(low_price__lte=10))
        elif filterPrice == '7':
            productList = productList.filter(Q(low_price__gte=1)&Q(low_price__lte=5))
        elif filterPrice == '8':
            productList = productList.filter(low_price__lt=1)

    productList = getPage(request, productList,16)
    return render(request, 'zircos/search.html',locals())


def compare(request):
    return render(request, 'ucenter/compare.html',locals())


def products(request):
    active_nav_menu = 'products'
    if not request.user.is_authenticated():
        return redirect('signin')
    user = request.user
    user_profile = UserProfile.objects.get(user=user)
    wd = request.GET.get('word')
    store = request.GET.get('store')
    o = request.GET.get('o')
    pg = request.GET.get('page','1')
    Products.objects.filter(product_update_date__lte=now().date() + timedelta(days=-0),is_single=0).update(status=0)
    Products.objects.filter(trans_update_date__lte=now().date() + timedelta(days=-0),is_single=0).update(status_trans=0)
    #productList = Products.objects.filter(is_single=0,seven_growth_rates__gt=-0)
    productList = Products.objects.filter(is_single=0,seven_growth_rates__gt=-0.9)

    wordrank = SearchWords.objects.filter(user=user).order_by('-product_num')[:10]


    if 'compare_list' in request.COOKIES:
        if request.COOKIES['compare_list']:
            compare_list = request.COOKIES['compare_list']
            compare_list = urllib.unquote(compare_list)
            goods = compare_list.split(',')
            compare_goods = Products.objects.filter(product_id__in=goods)
            idx = [0,1,2,3,4]
            empty_goods = idx[compare_goods.count():]

    if wd:
        wd = wd.strip()
        try:
            word = SearchWords.objects.get(pk=wd)
            productList = Products.objects.filter(keyword=word)
            total = productList.count()
        except Exception as e:
            return render(request, 'aside/error.html', {'msg': str(e)})
    else:
        total = productList.count()

    if store:
        store = store.strip()
        try:
            productList = Products.objects.filter(store_id=store)
            total = productList.count()
        except Exception as e:
            return render(request, 'aside/error.html', {'msg': str(e)})
    else:
        total = productList.count()

    productList = getPage(request, productList,16)

    return render(request, 'zircos/products.html',locals())



def monitorStore(request):
    if not request.user.is_authenticated():
        #return redirect('signin')
        return redirect('/ucenter/login/'+"?from="+request.get_full_path())
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        msg = "未授权用户"
        return render(request, 'aside/error.html', locals())
    try:
        user_profile = UserProfile.objects.get(user=user)
    except:
        return redirect('profile')
    storeList = Store.objects.filter(monitor_user=user)

    Products.objects.filter(add_date__lte=now().date() + timedelta(days=-30),is_new=1).update(is_new=0)

    if request.method == 'POST':
        storeId = request.POST.get('storeId')
        if storeId:
            try:
                store = Store.objects.get(store_id=storeId)
                store.monitor_user.add(user)
            except:
                store_link = 'https://www.aliexpress.com/store/%s'%(storeId)
                try:
                    data = getStoreInfo(storeId)
                    #print(data)
                    dt = datetime.datetime.strptime(data['openTime'], "%Y-%m-%d")
                    PositiveFeedback = float(data['PositiveFeedback'])
                    st = Store.objects.create(store_id=storeId,store_link=store_link,open_time=dt,seller=data['Seller'],
                                              positive_feedback=float(data['PositiveFeedback']),feedback_score=int(data['FeedbackScore']),
                                              item_as_described=float(data['ItemAsDescribed']),communication=float(data['Communication']),
                                              shipping_speed=float(data['ShippingSpeed']))
                    st.monitor_user.add(user)
                except Exception as e:
                    print(e)

    return render(request, 'velonic/ucenter-store.html',locals())


def myStore(request):
    if not request.user.is_authenticated():
        #return redirect('signin')
        return redirect('/ucenter/login/'+"?from="+request.get_full_path())
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        msg = "未授权用户"
        return render(request, 'aside/error.html', locals())
    try:
        user_profile = UserProfile.objects.get(user=user)
    except:
        return redirect('profile')
    storeList = Store.objects.filter(manage_user=user)

    Products.objects.filter(add_date__lte=now().date() + timedelta(days=-30),is_new=1).update(is_new=0)

    if request.method == 'POST':
        storeId = request.POST.get('storeId')
        if storeId:
            try:
                store = Store.objects.get(store_id=storeId)
                store.manage_user.add(user)
            except:
                store_link = 'https://www.aliexpress.com/store/%s'%(storeId)
                try:
                    data = getStoreInfo(storeId)
                    #print(data)
                    dt = datetime.datetime.strptime(data['openTime'], "%Y-%m-%d")
                    PositiveFeedback = float(data['PositiveFeedback'])
                    st = Store.objects.create(store_id=storeId,store_link=store_link,open_time=dt,seller=data['Seller'],
                                              positive_feedback=float(data['PositiveFeedback']),feedback_score=int(data['FeedbackScore']),
                                              item_as_described=float(data['ItemAsDescribed']),communication=float(data['Communication']),
                                              shipping_speed=float(data['ShippingSpeed']))
                    #st.monitor_user.add(user)
                    st.manage_user.add(user)
                except Exception as e:
                    print(e)

    return render(request, 'velonic/ucenter-mystore.html',locals())



def monitorStoreNew(request):
    if not request.user.is_authenticated():
        #return redirect('signin')
        return redirect('/ucenter/login/'+"?from="+request.get_full_path())
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        msg = "未授权用户"
        return render(request, 'aside/error.html', locals())
    try:
        user_profile = UserProfile.objects.get(user=user)
    except:
        return redirect('profile')

    st = request.GET.get('st')
    sj = request.GET.get('sj','30')

    Products.objects.filter(add_date__lte=now().date() + timedelta(days=-30),is_new=1).update(is_new=0)


    storeList = Store.objects.filter(monitor_user=user)

    storeIds = storeList.values('store_id')
    products = Products.objects.filter(store_id__in=storeIds,is_new=1)
    if st:
        products = products.filter(store_id=st)

    if sj:
        products = products.filter(add_date__gte=now().date() + timedelta(days=-int(sj)))
    return render(request, 'velonic/ucenter-store-new.html',locals())



def monitorStoreHot(request):
    if not request.user.is_authenticated():
        #return redirect('signin')
        return redirect('/ucenter/login/'+"?from="+request.get_full_path())
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        msg = "未授权用户"
        return render(request, 'aside/error.html', locals())
    try:
        user_profile = UserProfile.objects.get(user=user)
    except:
        return redirect('profile')

    Products.objects.filter(add_date__lte=now().date() + timedelta(days=-30),is_new=1).update(is_new=0)

    st = request.GET.get('st')
    sd = request.GET.get('sd','30')

    storeList = Store.objects.filter(monitor_user=user)

    storeIds = storeList.values('store_id')
    products = Products.objects.filter(store_id__in=storeIds,score__gt=1)
    if st:
        products = products.filter(store_id=st)

    if sd:
        products = products.filter(first_order_date__gte=now().date() + timedelta(days=-int(sd)))

    return render(request, 'velonic/ucenter-store-hot.html',locals())



def monitor_products(request):
    is_monitor_products = True
    avtive_nav_menu = 'monitor'
    if not request.user.is_authenticated():
        #return redirect('signin')
        return redirect('/ucenter/login/'+"?from="+request.get_full_path())
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        msg = "未授权用户"
        return render(request, 'aside/error.html', locals())

    try:
        user_profile = UserProfile.objects.get(user=user)
    except:
        return redirect('profile')

    o = request.GET.get('o')
    pg = request.GET.get('page')
    #Products.objects.filter(update_date__lte=now().date() + timedelta(days=-0),monitor=1,is_remove=0).update(status=0,status_trans=0)
    Products.objects.filter(product_update_date__lte=now().date() + timedelta(days=-0),user=user).update(status=0)
    productList = Products.objects.filter(user=user,monitor_user=user).order_by('-orders')
    if o == '0':
        productList = productList.order_by('-product_update_date')
    elif o == '1':
        productList = productList.order_by('product_update_date')
    elif o == '2':
        productList = productList.order_by('-transactions_in_last_six_months')
    elif o == '3':
        productList = productList.order_by('transactions_in_last_six_months')
    elif o == '6':
        productList = productList.order_by('-average_star_rating')
    elif o == '7':
        productList = productList.order_by('average_star_rating')
    elif o == '8':
        productList = productList.order_by('-votes')
    elif o == '9':
        productList = productList.order_by('votes')
    elif o == '10':
        productList = productList.order_by('-orders')
    elif o == '11':
        productList = productList.order_by('orders')
    elif o == '12':
        productList = productList.order_by('-wish_list')
    elif o == '13':
        productList = productList.order_by('wish_list')
    elif o == '14':
        productList = productList.order_by('-low_price')
    elif o == '15':
        productList = productList.order_by('low_price')
    productList = getPage(request, productList,16)

    if 'compare_list' in request.COOKIES:
        if request.COOKIES['compare_list']:
            compare_list = request.COOKIES['compare_list']
            compare_list = urllib.unquote(compare_list)
            goods = compare_list.split(',')
            compare_goods = Products.objects.filter(product_id__in=goods)
            idx = [0,1,2,3,4]
            empty_goods = idx[compare_goods.count():]

    #return render(request, 'default/products_monitor.html',locals())
    #return render(request, 'velonic/monitor.html',locals())
    return render(request, 'zircos/monitor.html',locals())



def single_products(request):
    is_single_products = True
    if not request.user.is_authenticated():
        #return redirect('signin')
        return redirect('/ucenter/login/'+"?from="+request.get_full_path())
    o = request.GET.get('o')
    pg = request.GET.get('page')
    Products.objects.filter(product_update_date__lte=now().date() + timedelta(days=-0),is_single=1).update(status=0,status_trans=0)
    productList = Products.objects.filter(is_single=1).order_by('-orders')
    if o == '0':
        productList = productList.order_by('-product_update_date')
    elif o == '1':
        productList = productList.order_by('product_update_date')
    elif o == '2':
        productList = productList.order_by('-transactions_in_last_six_months')
    elif o == '3':
        productList = productList.order_by('transactions_in_last_six_months')
    elif o == '6':
        productList = productList.order_by('-average_star_rating')
    elif o == '7':
        productList = productList.order_by('average_star_rating')
    elif o == '8':
        productList = productList.order_by('-votes')
    elif o == '9':
        productList = productList.order_by('votes')
    elif o == '10':
        productList = productList.order_by('-orders')
    elif o == '11':
        productList = productList.order_by('orders')
    elif o == '12':
        productList = productList.order_by('-wish_list')
    elif o == '13':
        productList = productList.order_by('wish_list')
    elif o == '14':
        productList = productList.order_by('-low_price')
    elif o == '15':
        productList = productList.order_by('low_price')
    productList = getPage(request, productList,20)
    return render(request, 'default/products.html',locals())



def product_info(request,productId):
    if not request.user.is_authenticated():
        #return redirect('signin')
        return redirect('/ucenter/login/'+"?from="+request.get_full_path())
    try:
        product = Products.objects.get(product_id=productId)
        transactions = Transaction.objects.filter(product_id=productId).order_by('-pub_date')
        total = transactions.count()

        allCountry = Country.objects.values('country_name_cn')

        try:
            package = Package.objects.get(product=product)
        except:
            pass

        #计算购买两件以上的用户（重复购买率）
        sql = 'select count(*) as count from `transaction` WHERE product_id=%s group by username having count>1'%(productId)
        transTow = my_custom_sql(sql)
        transTowNum = len(transTow)
        if total:
            transTowRate = round(float(transTowNum)/total,2)*100
        else:
            transTowRate = 0

        #return render(request, 'velonic/product-detail.html',locals())
        return render(request, 'zircos/product-detail.html',locals())
    except Exception as e:
        print(e)
        msg = '产品ID有误'
        return render(request,'aside/error.html',locals())


@csrf_exempt
def search_product_list(request):
    data = dict()
    if not request.user.is_authenticated():
        return redirect('signin')
    user = request.user
    if user.groups.filter(name='超级管理').count() > 0 :
        is_vip = True
        roles = '超级管理'
    elif user.groups.filter(name='VIP').count() > 0 :
        is_vip = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_regular = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_try = True
        roles = '试用用户'
    else:
        data["error"]=1
        data["msg"] = "没有权限操作！"
        return HttpResponse(json.dumps(data),content_type='application/json')

    wordId = request.POST.get('wordId')
    if wordId:
        word = SearchWords.objects.get(id=wordId)
        if word:
            results = 0
            try:
                for p in range(1,4):
                    if word.url:
                        html = getHtmlbyUrl(word.url,p)
                    else:
                        #print(word.keyword)
                        html = getHtml(str(word.keyword),p)
                    block = html[0]
                    results = html[1]
                    for i in block:
                        #item = getItem(i)
                        item = getItemMore(i)
                        if item.has_key('orders'):
                            if int(item["orders"])>word.order_filter:
                                if int(item['orders']) == 0:
                                    is_new = True
                                else:
                                    is_new = False
                                try:
                                    product = Products.objects.create(category_id=item["categoryID"],product_id=item["productID"],product_title=item["productTitle"],is_new=is_new,
                                                                       store_id=item["storeID"],product_link=item["productLink"],thumb=item["thumb"],
                                                                       low_price=float(item["lowPrice"]),high_price=float(item["highPrice"]),currency=item["currency"],
                                                                       average_star_rating=float(item["starRating"]),votes=int(item["votes"]),transactions_in_last_six_months=int(item["orders"])
                                                                       )
                                    product.keyword.add(word.id)
                                    #product.user.add(user)
                                    product.status=1
                                    product.product_update_date = datetime.datetime.now()
                                    product.save()
                                except Exception as e:
                                    try:
                                        product = Products.objects.get(product_id=item["productID"])
                                        #要更新产品信息的
                                        product.thumb=item["thumb"]
                                        product.low_price=float(item["lowPrice"])
                                        product.high_price=float(item["highPrice"])
                                        product.average_star_rating=float(item["starRating"])
                                        product.votes=int(item["votes"])
                                        product.orders=int(item["orders"])
                                        product.keyword.add(word.id)
                                        #product.user.add(user)
                                        product.status=1
                                        product.product_update_date = datetime.datetime.now()
                                        product.save()
                                    except:
                                        continue
                    if html[2]:
                        print('已经是最后一页')
                        break

                word.status = 1
                word.results= results
                word.product_num = Products.objects.filter(keyword=word).count()
                word.update_time = datetime.datetime.now()
                word.save()

                dt = datetime.datetime.now().date()
                try:
                    WordTrends.objects.create(word=word,results=results,track_date=dt,product_num=word.product_num)
                except Exception as e:
                    print(e)
                    wt = WordTrends.objects.get(word=word,track_date=dt)
                    wt.results=results
                    wt.product_num=word.product_num
                    wt.save()

                data["results"]=results
                data["product_num"]=word.product_num
                if word.update_time:
                    data["update_time"]=datetime.datetime.strftime(word.update_time, '%Y/%m/%d %H:%M:%S')
                else:
                    data["update_time"]=datetime.datetime.strftime(datetime.datetime.now(), '%Y/%m/%d %H:%M:%S')
                data["error"]=0
                return HttpResponse(json.dumps(data),content_type='application/json')
            except Exception as e:
                print(e)
                data["error"]=1
                data["msg"] = "解析数据失败！"
                return HttpResponse(json.dumps(data),content_type='application/json')
        else:
            data["error"]=1
            data["msg"] = "采集失败，过几分钟再重试."
            return HttpResponse(json.dumps(data),content_type='application/json')
    else:
        data["error"]=1
        data["msg"] = "无效的关键词ID"
        return HttpResponse(json.dumps(data),content_type='application/json')


def product_detail(request):
    limit = request.GET.get('limit')
    try:
        limit = int(limit)
    except:
        limit = 1000
    if limit:
        productList = Products.objects.filter(status=0).values("product_id","product_link")[:limit]

    return HttpResponse("采集单条产品基本信息")

@csrf_exempt
def single_product_detail(request):
    productId = request.POST.get('productId')
    product = Products.objects.get(product_id=productId)
    data = dict()
    if product:
        if 'http' in product.product_link:
            product_link = product.product_link
        else:
            product_link = 'https:%s'%(product.product_link)
        #result = get_productinfo(productId,product_link)
        #print(result)
        result = get_productionMore(product_link)
        if result == 404:
            data["error"] = 1
            data["msg"] = "404错误！产品已经下架。"
            product.is_remove=1
            product.save()
            return HttpResponse(json.dumps(data),content_type='application/json')
        if result == 301:
            data["error"] = 1
            data["msg"] = "301重定向（临时屏蔽）！过几分钟再重试！"
            return HttpResponse(json.dumps(data),content_type='application/json')
        else:
            try:
                product.high_price = float(result["highPrice"])
                product.low_price = float(result["lowPrice"])
                product.discount = int(result["discount"])
                product.wish_list = int(result["wishList"])
                product.orders = int(result["orders"])
                product.votes = int(result["votes"])
                product.average_star_rating = float(result["averageStarRating"])

                product.status=1
                product.product_update_date = datetime.datetime.now()
                product.save()


                dt = datetime.datetime.now().date()
                product_title = result["productTitle"]
                thumb = result["thumb"]
                try:
                    ProductTrends.objects.create(product=product,track_date=dt,product_title=product_title,thumb=thumb,
                                                 high_price=float(result["highPrice"]),low_price=float(result["lowPrice"]),
                                                 discount=int(result["discount"]),wish_list=int(result["wishList"]),
                                                 orders=int(result["orders"]),votes=int(result["votes"]),
                                                 average_star_rating=float(result["averageStarRating"]))
                except Exception as e:
                    print(e)


                data["error"] = 0
                data["highPrice"] = round(float(result["highPrice"])*(100-int(result["discount"]))/100,2)
                data["lowPrice"] = round(float(result["lowPrice"])*(100-int(result["discount"]))/100,2)
                data["wishList"] = result["wishList"]
                data["orders"] = result["orders"]
                data["votes"] = result["votes"]
                data["discount"] = result["discount"]
                data["averageStarRating"] = result["averageStarRating"]

                return HttpResponse(json.dumps(data),content_type='application/json')

            except Exception as e:
                data["error"] = 1
                data["msg"] = str(e)
                return HttpResponse(json.dumps(data),content_type='application/json')

    else:
        data["error"] = 1
        data["msg"] = "产品ID有误，没有找到产品！"
        return HttpResponse(json.dumps(data),content_type='application/json')


@csrf_exempt
def collectOneKey(request):
    productId = request.POST.get('productId')
    data = dict()
    if productId:
        product = Products.objects.get(product_id=productId)
        if product.trans_update_date == None or product.trans_update_date == '' or product.trans_update_date.date() != datetime.datetime.now().date():
            link = product.product_link
            if not 'http' in link:
                link = 'http:%s'%(product.product_link)

            #将链接转换为店铺链接
            if '/item/' in link:
                link = link.replace('/item/','/store/product/')
                link = link.replace(productId,'%s_%s'%(product.store_id,productId))

            result = get_productionMore(link)
            if result:
                if 404 == result:
                    product.is_remove = True
                    product.save()
                    data["error"] = 1
                    data["msg"] = "产品已经下架！"
                elif 301 == result:
                    data["error"] = 1
                    data["msg"] = "速卖通平台临时屏蔽，稍后再重试！"
                else:
                    if result.has_key('highPrice'):
                        product.high_price = float(result["highPrice"])
                    if result.has_key('lowPrice'):
                        product.low_price = float(result["lowPrice"])
                    if result.has_key('discount'):
                        product.discount = int(result["discount"])
                    if result.has_key('wishList'):
                        product.wish_list = int(result["wishList"])
                    if result.has_key('orders'):
                        product.orders = int(result["orders"])
                    if result.has_key('votes'):
                        product.votes = int(result["votes"])
                    if result.has_key('averageStarRating'):
                        product.average_star_rating = float(result["averageStarRating"])
                    if result.has_key('productLink'):
                        product.product_link = result["productLink"]
                    if result.has_key('productTitle'):
                        product.product_title = result["productTitle"]
                    if result.has_key('storeID'):
                        product.store_id = int(result["storeID"])
                    if result.has_key('categoryID'):
                        product.category_id = int(result["categoryID"])
                    if result.has_key('thumb'):
                        product.thumb = result["thumb"]
                    if result.has_key('currency'):
                        product.currency = result["currency"]

                    product.product_update_date = datetime.datetime.now()
                    product.status=1
                    product.save()


                    for page in range(1,202):
                        try:
                            transData = get_transactions(productId,page)
                        except Exception as e:
                            print(e)
                            transData = 0
                            data["error"] = 1
                            data["msg"] = "交易记录采集失败！"
                            data["msg"] += ":%s"%str(e)
                                #print(transData)
                        if transData:
                            transData = json.loads(transData)
                            transactions = transData['range']['transactions']
                            totalPage = transData['page']['total']
                            currentPage = transData['page']['current']
                            transactionsList = transData['records']
                            totalTrans = int(transactions)
                            if page==1:
                                product.transactions_in_last_six_months = totalTrans
                                product.save()
                            if page == int(currentPage):
                                for index,trans in enumerate(transactionsList):
                                    transId = trans['id']
                                    evaluationId = trans['evaluationId']
                                    userName = trans['name']
                                    countryCode = trans['countryCode']
                                    countryName = trans['countryName']
                                    buyerAccountPointLeval = trans['buyerAccountPointLeval']
                                    price = trans['price']
                                    quantity = trans['quantity']
                                    unit = trans['unit']
                                    lotNum = trans['lotNum']
                                    star = trans['star']
                                    if star=="":
                                        star="0"
                                    transDate = str(filter_date(trans['date']))
                                    buyerFeedback = trans['buyerFeedback']
                                    supplierReply = trans['supplierReply']
                                    buyerReply = trans['buyerReply']
                                    canDigg = trans['canDigg']
                                    if canDigg!="0":
                                        diggUp = trans['diggUp']
                                        diggDown = trans['diggDown']
                                        hasDigg = trans['hasDigg']
                                    else:
                                        diggUp = "0"
                                        diggDown = "0"
                                        hasDigg = "0"

                                    try:
                                        trans = Transaction.objects.create(product_id=int(result["productID"]),trans_id=transId,evaluation_id=evaluationId,username=userName,
                                                                           country_code=countryCode,country_name=countryName,buyer_leval=buyerAccountPointLeval,
                                                                           price = float(price),quantity=int(quantity),unit=unit,lot_num=int(lotNum),
                                                                           star=int(star),pub_date=transDate,buyer_feedback=buyerFeedback,supplier_reply=supplierReply,
                                                                           buyer_reply=buyerReply,digg_up=int(diggUp),digg_down=int(diggDown),
                                                                           has_digg=int(hasDigg),can_digg=int(canDigg))
                                        trans.save()
                                    except Exception as e:
                                        print(e)
                                        continue
                            else:
                                #print(u"已经最后一页。。。")
                                product.status_trans=1
                                product.trans_update_date = datetime.datetime.now()
                                product.save()
                                break

                        else:
                            product.save()
                            data["error"] = 1
                            data["msg"] = "采集交易记录失败"

                    #计算
                    #calcScore(productId)
                    info = calculateScore(productId)
                    updateFirstOrderTime(productId)

                    data["error"] = 0
                    data["transactions"]=product.transactions_in_last_six_months
                    data["weekly_orders"] = int(info["weekly_orders"])
                    data["orders"] = product.orders
                    data["votes"] = product.votes
                    data["wishList"] = product.wish_list
                    data["highPrice"] = round(float(result["highPrice"])*(100-int(result["discount"]))/100,2)
                    data["lowPrice"] = round(float(result["lowPrice"])*(100-int(result["discount"]))/100,2)
                    data["averageStarRating"] = result["averageStarRating"]
                    data["monthly_growth_rates"] = info["thirty"]
                    data["fifteen_growth_rates"] = info["fifteen"]
                    data["seven_growth_rates"] = info["seven"]
                    data["three_growth_rates"] = info["three"]

                    tmp = info["topSaleCountry"]
                    countrys = ''
                    if tmp:
                        tmp = tmp.split(',')

                        for item in tmp:
                            country_name = countryTanslate(item)
                            if country_name:
                                countrys += country_name
                                countrys += ','
                        data["top_sale_country"] = countrys.strip(',')
                    else:
                        data["top_sale_country"] = ''

            else:
                data["error"] = 1
                data["msg"] = "采集失败，请稍后再重试！"
            return HttpResponse(json.dumps(data),content_type='application/json')
        else:
            data["error"] = 1
            data["msg"] = "已经是最新数据！"
            return HttpResponse(json.dumps(data),content_type='application/json')
    else:
        data["error"] = 1
        data["msg"] = "采集失败！"
        return HttpResponse(json.dumps(data),content_type='application/json')



@csrf_exempt
def single_product_transactions(request):
    productId = request.POST.get('productId')
    data = {}
    data["error"]=1
    product = Products.objects.get(product_id=productId)
    startPage = 1
    totalTrans =0
    if productId:
        for page in range(startPage,202):
            try:
                transData = get_transactions(productId,page)
            except Exception as e:
                print(e)
                transData = 0
                data["msg"]="采集失败！"
                return HttpResponse(json.dumps(data),content_type='application/json')
            #print(transData)
            if transData:
                transData = json.loads(transData)
                transactions = transData['range']['transactions']
                totalPage = transData['page']['total']
                currentPage = transData['page']['current']
                transactionsList = transData['records']
                totalTrans = int(transactions)
                if page==1:
                    product.transactions_in_last_six_months = totalTrans
                    product.save()
                if page == int(currentPage):
                    for index,trans in enumerate(transactionsList):
                        transId = trans['id']
                        evaluationId = trans['evaluationId']
                        userName = trans['name']
                        countryCode = trans['countryCode']
                        countryName = trans['countryName']
                        buyerAccountPointLeval = trans['buyerAccountPointLeval']
                        price = trans['price']
                        quantity = trans['quantity']
                        unit = trans['unit']
                        lotNum = trans['lotNum']
                        star = trans['star']
                        if star=="":
                            star="0"
                        transDate = str(filter_date(trans['date']))
                        buyerFeedback = trans['buyerFeedback']
                        supplierReply = trans['supplierReply']
                        buyerReply = trans['buyerReply']
                        canDigg = trans['canDigg']
                        if canDigg!="0":
                            diggUp = trans['diggUp']
                            diggDown = trans['diggDown']
                            hasDigg = trans['hasDigg']
                        else:
                            diggUp = "0"
                            diggDown = "0"
                            hasDigg = "0"

                        try:
                            trans = Transaction.objects.create(product_id=productId,trans_id=transId,evaluation_id=evaluationId,username=userName,
                                                               country_code=countryCode,country_name=countryName,buyer_leval=buyerAccountPointLeval,
                                                               price = float(price),quantity=int(quantity),unit=unit,lot_num=int(lotNum),
                                                               star=int(star),pub_date=transDate,buyer_feedback=buyerFeedback,supplier_reply=supplierReply,
                                                               buyer_reply=buyerReply,digg_up=int(diggUp),digg_down=int(diggDown),
                                                               has_digg=int(hasDigg),can_digg=int(canDigg))
                            trans.save()
                        except Exception as e:
                            print(e)
                            #data["msg"]="采集失败！"
                            continue
                else:
                    #print(u"已经最后一页。。。")
                    product.status_trans=1
                    product.trans_update_date = datetime.datetime.now()
                    product.save()
                    break

            else:
                product.save()
                data["msg"]="采集%d失败！"%(page)
                return HttpResponse(json.dumps(data),content_type='application/json')

        #更新计算数据
        #calcScore(productId)
        result = calculateScore(productId)
        updateFirstOrderTime(productId)
        data["error"]=0
        data["transactions"]=product.transactions_in_last_six_months
        data["update_time"] =  product.trans_update_date.strftime("%Y/%m/%d %H:%M:%S")
        if product.first_order_date:
            data["first_order_date"] =  product.first_order_date.strftime("%Y-%m-%d")
        else:
            data["first_order_date"] =  'None'
        data["add_date"] =  product.add_date.strftime("%Y/%m/%d")
        return HttpResponse(json.dumps(data),content_type='application/json')

    else:
        data["msg"]="产品ID错误！"
        return HttpResponse(json.dumps(data),content_type='application/json')

def transdata(request):
    productId = request.GET.get('productId')
    days = request.GET.get('days','180')
    transData = {}
    transData["error"]=1
    if productId:
        try:
            sql = 'SELECT DATE_FORMAT( `pub_date`, "%Y-%m-%d" ) as "pubdate", COUNT( * ) as "orders", SUM(`quantity` >1) as "multorder", '+\
                  'SUM(`price` * `quantity`) as "Money" FROM `transaction` WHERE product_id="%s" '%(productId)
            if days:
                sql += 'AND date_sub(curdate(), INTERVAL %s DAY) <= date(`pub_date`)'%days
            sql += 'GROUP BY DATE_FORMAT( `pub_date`, "%Y-%m-%d" )'
            conn=linkDB()
            cur=conn.cursor()
            cur.execute(sql)
            transData["RECORDS"] = []
            if cur:
                newDate = []
                for t in cur:
                    pubdate = t[0]
                    orders = bytes(t[1])
                    multorder = bytes(t[2])
                    money = str(t[3])
                    transData["RECORDS"].append({"pubdate":pubdate,"orders":orders,"multorder":multorder,"money":money})
                    newDate.append(pubdate)

                if len(transData["RECORDS"])>0:
                    calendar = datelist(transData["RECORDS"][0]['pubdate'],transData["RECORDS"][-1]['pubdate'])

                    '''
                    newDate = []
                    for item in transData["RECORDS"]:
                        newDate.append(str(item['pubdate']))
                    '''

                    for dt in calendar:
                        if dt not in newDate:
                            transData["RECORDS"].append({u'pubdate':unicode(dt),u'money':u'0.0',u'multorder':u'0',u'orders':u'0'})

                    transData["RECORDS"].sort(lambda x,y: cmp(x['pubdate'], y['pubdate']))
                    transData["RECORDS"] = sorted(transData["RECORDS"], key=lambda x:x['pubdate'])

                cur.close()
                conn.close()
                transData["error"]=0
            else:
                transData["msg"]=u"产品暂无交易信息！".decode()

        except Exception as e:
            print(e)
            transData["msg"]=u"查询失败！".decode()
    else:
        transData["msg"]=u"产品ID有误".decode()
    return HttpResponse(json.dumps(transData),content_type='application/json; charset=utf-8')


def transdataV2(request):
    productId = request.GET.get('productId')
    transData = {}
    transData["error"]=1
    if productId:
        try:
            sql = 'SELECT DATE_FORMAT( `pub_date`, "%Y-%m-%d" ) as "pubdate", COUNT( * ) as "orders", SUM(`quantity` >1) as "multorder", '+\
                  'SUM(`price` * `quantity`) as "Money" FROM `transaction` WHERE product_id="%s" '%(productId)+\
                  'GROUP BY DATE_FORMAT( `pub_date`, "%Y-%m-%d" )'
            conn=linkDB()
            cur=conn.cursor()
            cur.execute(sql)
            transData["RECORDS"] = []
            if cur:
                for t in cur:
                    pubdate = t[0]
                    orders = bytes(t[1])
                    multorder = bytes(t[2])
                    money = str(t[3])
                    transData["RECORDS"].append({"pubdate":pubdate,"orders":orders,"multorder":multorder,"money":money})
                cur.close()
                conn.close()
                transData["error"]=0
            else:
                transData["msg"]="产品暂无交易信息！"

        except Exception as e:
            print(e)
            transData["msg"]="查询失败！"
    else:
        transData["msg"]="产品ID有误"
    return HttpResponse(json.dumps(transData),content_type='application/json')


def countryrank(request):
    productId = request.GET.get('productId')
    transData = {}
    transData["error"]=1
    if productId:
        try:
            count = Transaction.objects.filter(product_id=productId).count()
            sql = 'SELECT country_code, COUNT( * ) as "orders", country_name ,COUNT(*)/%d as "percent" '%(count)+\
                  'FROM `transaction` WHERE product_id="%s" '%(productId)+\
                  'GROUP BY country_code ORDER BY orders desc'
            conn=linkDB()
            cur=conn.cursor()
            cur.execute(sql)
            transData["RECORDS"] = []
            if cur:
                for t in cur:
                    country_name = countryTanslate(t[0])
                    if country_name == 0:
                        country_name = t[2]
                    orders = bytes(t[1])
                    percent = str(t[3])
                    transData["RECORDS"].append({"country_name":country_name,"orders":orders,"percent":percent})
                cur.close()
                conn.close()
                transData["error"]=0
            else:
                transData["msg"]="产品暂无交易信息！"

        except Exception as e:
            print(e)
            transData["msg"]="查询失败！"
    else:
        transData["msg"]="产品ID有误"
    return HttpResponse(json.dumps(transData),content_type='application/json')

def getsales(request):
    productId = request.GET.get('productId')
    salesData = {}
    salesData["error"]=1
    if productId:
        try:
            sql1 = 'SELECT sum(price * quantity)'+\
                  'FROM `transaction` WHERE product_id="%s"; '%(productId)

            conn=linkDB()
            cur=conn.cursor()
            cur.execute(sql1)
            if cur:
                for t in cur:
                    salesData["TotalSales"] = str(t[0])
            sql = 'SELECT MIN(`pub_date`), MAX(`pub_date`) FROM `transaction` WHERE product_id="%s";'%(productId)
            cur.execute(sql)
            if cur:
                for t in cur:
                    salesData["MinDay"] = str(t[0])
                    salesData["MaxDay"] = str(t[1])
            if salesData.has_key('MaxDay'):
                sql = 'SELECT sum(price * quantity) '+\
                    'FROM `transaction` WHERE product_id="%s" '%(productId)+\
                    'AND date_sub(curdate(), INTERVAL 30 DAY) <= date(`pub_date`);'
                    #'AND date_sub(date("'+salesData["MaxDay"]+'"), INTERVAL 30 DAY) <= date(`pub_date`);'
                cur.execute(sql)
                if cur:
                    for t in cur:
                        salesData["MonthSalse"] = str(t[0])
                sql = 'SELECT sum(price * quantity) '+\
                    'FROM `transaction` WHERE product_id="%s" '%(productId)+\
                    'AND date_sub(curdate(), INTERVAL 7 DAY) <= date(`pub_date`);'
                    #'AND date_sub(date("'+salesData["MaxDay"]+'"), INTERVAL 7 DAY) <= date(`pub_date`);'
                cur.execute(sql)
                if cur:
                    for t in cur:
                        salesData["WeekSalse"] = str(t[0])
            cur.close()
            conn.close()
            salesData["error"]=0

        except Exception as e:
            salesData["msg"]= str(e)
    else:
        salesData["msg"]=u"产品ID有误"
    return HttpResponse(json.dumps(salesData),content_type='application/json; charset=utf-8')

def userrank(request):
    productId = request.GET.get('productId')
    transData = {}
    transData["error"]=1
    if productId:
        try:
            count = Transaction.objects.filter(product_id=productId).count()
            sql = 'SELECT buyer_leval, COUNT( * ) as "orders", COUNT(*)/%d as "percent" '%(count)+\
                  'FROM `transaction` WHERE product_id="%s" '%(productId)+\
                  'GROUP BY buyer_leval ORDER BY orders desc'
            conn=linkDB()
            cur=conn.cursor()
            cur.execute(sql)
            transData["RECORDS"] = []
            if cur:
                for t in cur:
                    buyer_leval = str(t[0])
                    orders = bytes(t[1])
                    percent = str(t[2])
                    transData["RECORDS"].append({"buyer_leval":buyer_leval,"orders":orders,"percent":percent})
                cur.close()
                conn.close()
                transData["error"]=0
            else:
                transData["msg"]="产品暂无交易信息！"

        except Exception as e:
            print(e)
            transData["msg"]="查询失败！"
    else:
        transData["msg"]="产品ID有误"
    return HttpResponse(json.dumps(transData),content_type='application/json')


def quantityrank(request):
    productId = request.GET.get('productId')
    transData = {}
    transData["error"]=1
    if productId:
        try:
            count = Transaction.objects.filter(product_id=productId).count()
            sql = 'SELECT quantity,count(*) as "amount" ,count(*)/%d as "percent" FROM `transaction` WHERE product_id=%s GROUP BY quantity;'%(count,productId)
            conn=linkDB()
            cur=conn.cursor()
            cur.execute(sql)
            transData["RECORDS"] = []
            if cur:
                for t in cur:
                    quantity = int(t[0])
                    amount = int(t[1])
                    percent = str(t[2])
                    transData["RECORDS"].append({"quantity":quantity,"amount":amount,"percent":percent})
                cur.close()
                conn.close()
                transData["error"]=0
            else:
                transData["msg"]="产品暂无交易信息！"

        except Exception as e:
            print(e)
            transData["msg"]="查询失败！"
    else:
        transData["msg"]="产品ID有误"
    return HttpResponse(json.dumps(transData),content_type='application/json')


def test(request):
    results = 10288
    product_num = 123
    stat = dict()
    stat["results"]=results
    stat["product_num"]=product_num
    stat["error"]=0
    return HttpResponse(json.dumps(stat),content_type='application/json')


def get_test(request):
    return render(request, 'default/test.html',locals())


def open_product(request,productId):
    try:
        product = Products.objects.get(product_id=productId)
    except:
        msg = "无效的产品ID"
        return render(request, 'aside/error.html',locals())
    if 'http' in product.product_link:
        productLink = product.product_link
    else:
        productLink = 'http:%s'%(product.product_link)
    return render(request, 'default/frame.html',locals())


def recoverPassword(request):
    return render(request, 'velonic/recover-password.html',locals())

def changePassword(request):
    if not request.user.is_authenticated():
        return redirect('signin')

    oldPassword = request.POST.get('oldPassword',None)
    newPassword = request.POST.get('newPassword',None)
    comfirmPassword = request.POST.get('comfirmPassword',None)

    if oldPassword and newPassword and comfirmPassword:
        pass

        user = authenticate(username=request.user.username,password=oldPassword.strip())
        if user is not None:
            newuser = User.objects.get(username__exact=request.user.username)
            newuser.set_password(newPassword.strip())
            newuser.save()
            logout(request)
            return redirect('signin')
        else:
            return render(request, 'velonic/error.html', {'msg': u'验证失败！'})

    return render(request, 'velonic/changepassword.html',locals())

def signin(request):
    referrer=request.GET.get('from')
    if request.user.is_authenticated():
        return redirect('ucenter')
    try:
        if request.method == 'POST':
            login_form = LoginForm(request.POST)
            if login_form.is_valid():
                # 登录
                username = login_form.cleaned_data["username"]
                password = login_form.cleaned_data["password"]
                user = authenticate(username=username, password=password)
                if user is not None:
                    user.backend = 'django.contrib.auth.backends.ModelBackend' # 指定默认的登录验证方式
                    login(request, user)
                else:
                    return render(request, 'aside/error.html', {'msg': '登录验证失败'})
                #return redirect(request.POST.get('source_url'))

                if request.POST.get('source_url') !=None and request.POST.get('source_url') !='' and request.POST.get('source_url') !='None':
                    #print(request.POST.get('source_url'))
                    return redirect(request.POST.get('source_url'))
                else:
                    return redirect('home')

            else:
                return render(request, 'aside/error.html', {'msg': login_form.errors})
        else:
            login_form = LoginForm()
    except Exception as e:
        return render(request, 'aside/error.html', {'msg': str(e)})
    #return render(request, 'default/login.html', locals())
    #return render(request, 'velonic/login.html', locals())
    return render(request, 'aside/user/login.html', locals())

# 注销
def signout(request):
    try:
        logout(request)
    except Exception as e:
        return render(request, 'velonic/error.html', {'msg': str(e)})
    return redirect(request.META['HTTP_REFERER'])


# 分页代码,传入每页记录条数
def getPage(request, product_list,number,after_range_num = 5,bevor_range_num = 4):
    paginator = Paginator(product_list, number)
    page = int(request.GET.get('page', 1))
    try:
        product_list = paginator.page(page)
    except (EmptyPage, InvalidPage, PageNotAnInteger):
        product_list = paginator.page(1)

    return product_list


def downloadcsv(request):
    if not request.user.is_authenticated():
        return render(request, 'aside/error.html', {'msg': '需要登录才能下载！'})
    productId = request.GET.get('id',None)
    typeId = request.GET.get('type',None)
    wd = request.GET.get('wd',None)
    if wd:
        word = SearchWords.objects.get(pk=wd)
    else:
        word = None
    if productId and typeId=='t':
        try:
            transactions = Transaction.objects.filter(product_id=productId).order_by('-pub_date')
            if transactions.count()<1:
                return render(request, 'aside/error.html', {'msg': '没有交易数据！'})
            response = HttpResponse(content_type='text/csv')
            response['Content-Disposition'] = 'attachment; filename="trans_%d_%s.csv"'%(int(time.time()),productId)
            writer = csv.writer(response)
            writer.writerow(['编号', '用户名', '国家', '买家等级','成交单价','购买数量','批量','评分','成交时间'])
            for trans in transactions:
                writer.writerow([trans.id, trans.username, trans.country_name, trans.buyer_leval, trans.price, trans.quantity,trans.lot_num,trans.star,trans.pub_date])
            return response
        except Exception as e:
            return render(request, 'aside/error.html', {'msg': str(e)})
    elif word and typeId == 'p':
        try:
            products = word.products_set.filter(orders__gt=0)
            if products.count()<1:
                return render(request, 'aside/error.html', {'msg': '没有产品数据！'})
            response = HttpResponse(content_type='text/csv')
            response['Content-Disposition'] = 'attachment; filename="product_%d_%d.csv"'%(int(time.time()),word.id)
            writer = csv.writer(response)
            writer.writerow(['编号', '产品ID', '产品主图', '最低价','最高价','心愿单','成交量','投票','评分','近6月成交量','更新时间'])
            for p in products:
                writer.writerow([p.id, p.product_id, p.thumb,p.low_price,p.high_price, p.wish_list, p.orders,p.votes,p.average_star_rating,p.transactions_in_last_six_months,p.update_date])
            return response
        except Exception as e:
            return render(request, 'aside/error.html', {'msg': str(e)})
    else:
        return render(request, 'aside/error.html', {'msg': '地址有误，无法下载'})


def countryTanslate(countryCode):
    try:
        c = Country.objects.get(country_code=countryCode.upper())
        result = '%s(%s)'%(c.country_name_cn,c.country_code)
    except:
        result = 0
    return result


def single(request):
    if not request.user.is_authenticated():
        return redirect('signin')
    user = request.user
    link = request.POST.get('link')
    storeId = request.POST.get('storeId')
    if link:
        link = link.split('?')[0]
        link = link.replace('https','http')
        if storeId:
            if '/item/' in link:
                link = link.replace('/item/','/store/product/')
                a = link.split('/')[-1]
                b = '%s_%s'%(storeId,a)
                link = link.replace(a,b)

        try:
            result = get_productionMore(link)
            #print(result)
            if result:
                if result.has_key('productID'):
                    try:
                        product = Products.objects.create(product_id=result["productID"],is_single=1)
                        if not product:
                            return render(request, 'aside/error.html', {'msg': "创建产品失败！"})

                    except:
                        product = Products.objects.get(product_id=result["productID"])

                    #print(product)

                    if result.has_key('highPrice'):
                        product.high_price = float(result["highPrice"])
                    if result.has_key('lowPrice'):
                        product.low_price = float(result["lowPrice"])
                    if result.has_key('discount'):
                        product.discount = int(result["discount"])
                    if result.has_key('wishList'):
                        product.wish_list = int(result["wishList"])
                    if result.has_key('orders'):
                        product.orders = int(result["orders"])
                    if result.has_key('votes'):
                        product.votes = int(result["votes"])
                    if result.has_key('averageStarRating'):
                        product.average_star_rating = float(result["averageStarRating"])
                    if result.has_key('productLink'):
                        product.product_link = result["productLink"]
                    if result.has_key('productTitle'):
                        product.product_title = result["productTitle"]
                    if result.has_key('storeID'):
                        product.store_id = int(result["storeID"])
                    if result.has_key('categoryID'):
                        product.category_id = int(result["categoryID"])
                    if result.has_key('thumb'):
                        product.thumb = result["thumb"]
                    if result.has_key('currency'):
                        product.currency = result["currency"]

                    product.user.add(user)
                    product.product_update_date = datetime.datetime.now()
                    product.status=1
                    product.save()

                    for page in range(1,202):
                        try:
                            transData = get_transactions(result["productID"],page)
                        except Exception as e:
                            print(e)
                            transData = 0
                            return render(request, 'aside/error.html', {'msg': "采集交易纪录失败！"})
                        #print(transData)
                        if transData:
                            transData = json.loads(transData)
                            transactions = transData['range']['transactions']
                            totalPage = transData['page']['total']
                            currentPage = transData['page']['current']
                            transactionsList = transData['records']
                            totalTrans = int(transactions)
                            if page==1:
                                product.transactions_in_last_six_months = totalTrans
                                product.save()
                            if page == int(currentPage):
                                for index,trans in enumerate(transactionsList):
                                    transId = trans['id']
                                    evaluationId = trans['evaluationId']
                                    userName = trans['name']
                                    countryCode = trans['countryCode']
                                    countryName = trans['countryName']
                                    buyerAccountPointLeval = trans['buyerAccountPointLeval']
                                    price = trans['price']
                                    quantity = trans['quantity']
                                    unit = trans['unit']
                                    lotNum = trans['lotNum']
                                    star = trans['star']
                                    if star=="":
                                        star="0"
                                    transDate = str(filter_date(trans['date']))
                                    buyerFeedback = trans['buyerFeedback']
                                    supplierReply = trans['supplierReply']
                                    buyerReply = trans['buyerReply']
                                    canDigg = trans['canDigg']
                                    if canDigg!="0":
                                        diggUp = trans['diggUp']
                                        diggDown = trans['diggDown']
                                        hasDigg = trans['hasDigg']
                                    else:
                                        diggUp = "0"
                                        diggDown = "0"
                                        hasDigg = "0"

                                    try:
                                        trans = Transaction.objects.create(product_id=int(result["productID"]),trans_id=transId,evaluation_id=evaluationId,username=userName,
                                                                            country_code=countryCode,country_name=countryName,buyer_leval=buyerAccountPointLeval,
                                                                            price = float(price),quantity=int(quantity),unit=unit,lot_num=int(lotNum),
                                                                            star=int(star),pub_date=transDate,buyer_feedback=buyerFeedback,supplier_reply=supplierReply,
                                                                            buyer_reply=buyerReply,digg_up=int(diggUp),digg_down=int(diggDown),
                                                                            has_digg=int(hasDigg),can_digg=int(canDigg))
                                        trans.save()
                                    except Exception as e:
                                        print(e)
                                        continue
                            else:
                                #print(u"已经最后一页。。。")
                                product.status_trans=1
                                product.trans_update_date = datetime.datetime.now()
                                product.save()
                                break

                        else:
                            product.save()
                            return render(request, 'aside/error.html', {'msg': "采集交易纪录失败！"})

                    #计算更新数据
                    #calcScore(result["productID"])
                    info = calculateScore(result["productID"])
                    updateFirstOrderTime(result["productID"])

            else:
                return render(request, 'aside/error.html', {'msg': "采集产品失败！"})
        except Exception as e:
            return render(request, 'aside/error.html', {'msg': str(e)})

        return redirect('favorite')
    else:
        return render(request, 'aside/error.html', {'msg': "产品采集失败！"})


def favorite_add(request):
    if not request.user.is_authenticated():
        return HttpResponse(0)
    user = request.user
    if user.groups.filter(name='超级管理').count() > 0 :
        is_super = True
        roles = '超级管理'
    elif user.groups.filter(name='VIP').count() > 0 :
        is_vip = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_regular = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_try = True
        roles = '试用用户'
    else:
        return HttpResponse(0)
    productId = request.GET.get('id')
    wordId = request.GET.get('wordId')
    if productId:
        product = Products.objects.get(product_id=productId)
        product.user.add(user)
        return HttpResponse(1)
    elif wordId:
        wd = SearchWords.objects.get(pk=wordId)
        wd.user.add(user)
        '''
        #将关键词下的产品批量收藏
        plist = Products.objects.filter(keyword=wd)
        for p in plist:
            p.user.add(user)
        '''
        return HttpResponse(1)
    else:
        return HttpResponse(0)


def favorite_remove(request):
    if not request.user.is_authenticated():
        return HttpResponse(0)
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        return HttpResponse(0)

    productId = request.GET.get('id')
    wordId = request.GET.get('wordId')
    if productId:
        product = Products.objects.get(product_id=productId)
        product.user.remove(user)
        return HttpResponse(1)
    elif wordId:
        wd = SearchWords.objects.get(pk=wordId)
        wd.user.remove(user)
        plist = Products.objects.filter(keyword=wd)
        for p in plist:
            p.user.remove(user)
        return HttpResponse(1)
    else:
        return HttpResponse(0)


def monitor_add(request):
    if not request.user.is_authenticated():
        return HttpResponse(0)
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        return HttpResponse(0)

    try:
        productId = request.GET.get('id')
        if productId:
            product = Products.objects.get(product_id=productId)
            product.user.add(user)
            product.monitor_user.add(user)
            product.monitor = 1
            product.save()
            return HttpResponse(1)
    except:
        return HttpResponse(0)


def monitor_remove(request):
    if not request.user.is_authenticated():
        return HttpResponse(0)
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        return HttpResponse(0)

    try:
        productId = request.GET.get('id')
        if productId:
            product = Products.objects.get(product_id=productId)
            if product.user.filter(id=user.id).count() <= 0:
                return HttpResponse(0)
            product.monitor_user.remove(user)
            #product.is_remove = 1
            product.save()
            return HttpResponse(1)
    except:
        return HttpResponse(0)


def remove_store(request):
    if not request.user.is_authenticated():
        return HttpResponse(0)
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        return HttpResponse(0)

    try:
        storeId = request.GET.get('id')
        if storeId:
            store = Store.objects.get(pk=storeId)
            if store.monitor_user.filter(id=user.id).count() <= 0:
                return HttpResponse(0)
            else:
                store.monitor_user.remove(user)
                store.save()
                return HttpResponse(1)
        else:
            return HttpResponse(0)
    except Exception as e:
        #print(e)
        return HttpResponse(0)

def remove_product(request):
    if not request.user.is_authenticated():
        return HttpResponse(0)
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        return HttpResponse(0)
    try:
        productId = request.GET.get('id')
        if productId:
            product = Products.objects.get(product_id=productId)
            if product.user.filter(id=user.id).count() <= 0:
                msg = "您无权操作此产品"
                return render(request, 'aside/error.html', locals())
            product.monitor = 0
            product.monitor_user.remove(user)
            product.user.remove(user)
            #product.is_remove = 1
            product.save()
            return HttpResponse(1)
    except Exception as e:
        #print(e)
        return HttpResponse(0)


def logs(request):
    dir = "/Users/zhaoyoucai/PycharmProjects/myenv/tradetrends/logs"
    #flist = os.listdir(dir)
    #print(flist)
    logData = list()
    for fullname in iterbrowse(dir):
        data = dict()
        filename = fullname.split('/')[-1]
        data['year'] = filename.split('.')[0]
        data['month'] = filename.split('.')[1]
        data['day'] = filename.split('.')[2]
        data['items'] = ''
        f = open(fullname,'r')
        for line in f.readlines():
            data['items'] += '<li>'+unicode(line.strip())+'</li>'
        f.close()
        logData.append(data)
    #logData = json.dumps(logData)
    logData = logData[::-1]
    return render(request, 'default/logs.html', locals())



def logistics(request):
    if not request.user.is_authenticated():
        return redirect('signin')
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        msg = "未授权用户"
        return render(request, 'aside/error.html', locals())
    #yc = request.GET.get('yc')

    try:
        user_profile = UserProfile.objects.get(user=user)
    except:
        return redirect('profile')

    if user_profile.wuliu_yujing and user_profile.wuliu_yichang:
        wuliu_yj = user_profile.wuliu_yujing
        wuliu_yc = user_profile.wuliu_yichang
    else:
        wuliu_yj = 10
        wuliu_yc = 30

    if 'yc_chose' in request.COOKIES:
        yc = request.COOKIES['yc_chose']
    else:
        yc = None
    if 'yj_chose' in request.COOKIES:
        yj = request.COOKIES['yj_chose']
    else:
        yj = None
    try:
        wuliu = Logistics.objects.filter(user = user,mark=False)
        yujing = wuliu.exclude(status=0)
        yujing = yujing.exclude(status=1)
        yujing = yujing.exclude(status=2)
        yujing = yujing.exclude(status=6)
        yujing = yujing.exclude(status=8)

        if yj:
            yujing = yujing.filter(num1__gt=int(yj))
        else:
            yujing = yujing.filter(num1__gt=wuliu_yj)

        mindate = wuliu.order_by('send_time')[:1]
        mindate = mindate[0]
        maxdate = wuliu.order_by('-send_time')[:1]
        maxdate = maxdate[0]
        #print('执行爬虫抓取单号运输数据')
        yichang = wuliu.filter(status=3)
        if yc:
            yichang = yichang.filter(num1__gt=int(yc))
        else:
            yichang = yichang.filter(num1__gt=wuliu_yc)

        #sql = "SELECT destination as 'Country',channel as 'Channel',round(avg(num1)) as 'Days',count(*) as 'Amount',concat(left(sum(`status`=2 or `status`=1)/count(*)*100,5),'%') as 'DSR' FROM logistics WHERE date_sub(curdate(), INTERVAL 90 DAY) <= date(`send_time`) GROUP BY destination ORDER BY count(*) DESC LIMIT 10"
        sql = "SELECT destination as 'Country',channel as 'Channel',round(avg(num1)) as 'Days',count(*) as 'Amount',left(sum(`status`=2 or `status`=1)/count(*)*100,5) as 'DSR' FROM logistics WHERE date_sub(curdate(), INTERVAL 90 DAY) <= date(`send_time`) GROUP BY destination ORDER BY count(*) DESC LIMIT 10"
        countryData = my_custom_sql(sql)
        sql = 'SELECT channel as "Channel",sum(`status`=2 or `status`=1) as "Amount",concat(left(sum(`status`=2 or `status`=1)/count(*)*100,5),"%") as "DSR",round(avg(num1)) as "Days" FROM logistics WHERE date_sub(curdate(), INTERVAL 90 DAY) <= date(`send_time`) GROUP BY channel ORDER BY sum(`status`=2 or `status`=1) DESC LIMIT 10'
        channelData = my_custom_sql(sql)
    except:
        pass

    #return render(request, 'ucenter/logistics.html', locals())
    return render(request, 'velonic/logistics.html', locals())


def getovertime(request):
    if not request.user.is_authenticated():
        return redirect("signin")
    try:
        channel = request.GET.get('channel')
        if channel:
            sql = 'SELECT destination as "Country" FROM logistics WHERE channel="%s" and num1>30 AND (`status`=1 or `status`=2) GROUP BY destination ORDER BY COUNT(*) DESC '%(channel)
            info = my_custom_sql(sql)
            return HttpResponse(json.dumps(info),content_type='application/json')

    except Exception as e:
        print(e)
    return HttpResponse('获取数据失败！')


def getExceptionRate(request):
    if not request.user.is_authenticated():
        return redirect("signin")
    try:
        country = request.GET.get('country')
        if country:
            sql = 'SELECT left(sum(`status`=3 or num1>30)/count(*)*100,5) as "DSR" FROM logistics WHERE destination="%s"'%(country)
            info = my_custom_sql(sql)
            return HttpResponse(json.dumps(info),content_type='application/json')

    except Exception as e:
        print(e)
    return HttpResponse('获取数据失败！')


def getpinggu(request):
    if not request.user.is_authenticated():
        return redirect("signin")
    try:
        startDate = request.GET.get('sd')
        endDate = request.GET.get('ed')
        if startDate and endDate:
            #sql = "SELECT channel as 'Channel',round(avg(num1)) as 'Days',concat(left(sum(`status`=2 or `status`=1)/count(*)*100,5),'%') as 'DSR',count(*) as 'Amount' FROM logistics WHERE destination='%s' and date_sub(curdate(), INTERVAL 90 DAY) <= date(`send_time`) GROUP BY channel ORDER BY sum(`status`=2 or `status`=1)/count(*) DESC LIMIT 10"%(country)
            sql = 'SELECT channel as "Channel",sum(`status`=2 or `status`=1) as "Amount",left(sum(`status`=2 or `status`=1)/count(*)*100,5) as "DSR",round(avg(num1)) as "Days" FROM logistics WHERE date(`send_time`) >="%s" AND date(`send_time`) < "%s" GROUP BY channel ORDER BY sum(`status`=2 or `status`=1) DESC LIMIT 10'%(startDate,endDate)
            info = my_custom_sql(sql)
            return HttpResponse(json.dumps(info),content_type='application/json')

    except Exception as e:
        print(e)
    return HttpResponse('获取数据失败！')


def getinfo(request):
    if not request.user.is_authenticated():
        return redirect("signin")
    try:
        country = request.GET.get('country')
        channel = request.GET.get('channel')
        if country:
            #sql = "SELECT channel as 'Channel',round(avg(num1)) as 'Days',concat(left(sum(`status`=2 or `status`=1)/count(*)*100,5),'%') as 'DSR',count(*) as 'Amount' FROM logistics WHERE destination='%s' and date_sub(curdate(), INTERVAL 90 DAY) <= date(`send_time`) GROUP BY channel ORDER BY sum(`status`=2 or `status`=1)/count(*) DESC LIMIT 10"%(country)
            sql = 'SELECT channel as "Channel",sum(`status`=2 or `status`=1) as "Amount",concat(left(sum(`status`=2 or `status`=1)/count(*)*100,5),"%") as "DSR",round(avg(num1)) as "Days" FROM logistics WHERE date_sub(curdate(), INTERVAL 90 DAY) <= date(`send_time`) GROUP BY channel ORDER BY sum(`status`=2 or `status`=1) DESC LIMIT 10'
            info = my_custom_sql(sql)
            #print(info)
            #info = serialize('json',info)
            #print(tzlist)
            return HttpResponse(json.dumps(info),content_type='application/json')
        elif channel:
            sql = 'SELECT destination as "Country",round(avg(num1)) as "Days",left(sum(`status`=2 or `status`=1)/count(*)*100,5) as "DSR",count(*) as "Amount" FROM logistics WHERE channel="%s" and date_sub(curdate(), INTERVAL 90 DAY) <= date(`send_time`) GROUP BY destination ORDER BY round(avg(num1))  DESC LIMIT 10'%(channel)
            info = my_custom_sql(sql)
            return HttpResponse(json.dumps(info),content_type='application/json')
    except Exception as e:
        print(e)
    return HttpResponse('获取数据失败！')


def my_custom_sql(sql):
    from django.db import connection, transaction
    cursor = connection.cursor()

    # 数据修改操作——提交要求
    #cursor.execute(sql)
    #transaction.commit_unless_managed()

    # 数据检索操作,不需要提交
    cursor.execute(sql)
    rawData = cursor.fetchall()
    col_names = [desc[0] for desc in cursor.description]

    result = []
    for row in rawData:
        objDict = {}
        # 把每一行的数据遍历出来放到Dict中
        for index, value in enumerate(row):
            objDict[col_names[index]] = str(value)

        result.append(objDict)

    return result



def logistics_settings(request):
    if not request.user.is_authenticated():
        return redirect('signin')
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        msg = "未授权用户"
        return render(request, 'aside/error.html', locals())
    try:
        user_profile = UserProfile.objects.get(user=user)
    except:
        return redirect('profile')
    ls = LogisticsSetting.objects.filter(user=user)

    sql = 'SELECT channel as "Channel",COUNT(*) as "Count" FROM logistics GROUP BY channel ORDER BY COUNT(*) DESC;'
    channels = my_custom_sql(sql)
    sql = 'SELECT destination as "Destination",COUNT(*) as "Count" FROM logistics GROUP BY destination ORDER BY COUNT(*) DESC;'
    countrys = my_custom_sql(sql)

    if request.method == 'POST':
        wuliu = request.POST.get('inputWuliu')
        destination = request.POST.get('inputCountry')
        regulation = request.POST.get('inputRegulation')

        wuliu_yujing = request.POST.get('wuliu_yujing')
        wuliu_yichang = request.POST.get('wuliu_yichang')

        if wuliu:
            wuliu = wuliu.strip()
            destination = destination.strip()
            regulation = regulation.strip()


            if wuliu=='' or destination=='' or regulation=='':
                msg = "物流渠道、国家、调控数值不能为空"
                return render(request, 'aside/error.html', locals())

            else:
                try:

                    sql = 'SELECT avg(num1) as "Average" FROM logistics WHERE destination="%s" and channel="%s" and ( `status`=1 or `status`=2);'%(destination,wuliu)
                    data = my_custom_sql(sql)
                    average = data[0]
                    if not average['Average'] == None:
                        average = 0
                    else:
                        average = int(round(float(average['Average'])))
                    LogisticsSetting.objects.create(user=user,channel=wuliu,country=destination,regulation=int(regulation),average=average)
                except Exception as e:
                    print(e)
        elif wuliu_yujing:
            wuliu_yujing = wuliu_yujing.strip()
            wuliu_yichang = wuliu_yichang.strip()

            if wuliu_yujing and wuliu_yichang:
                try:
                    user_profile.wuliu_yujing = int(wuliu_yujing)
                    user_profile.wuliu_yichang = int(wuliu_yichang)
                    user_profile.save()
                except Exception as e:
                    print(e)

    #return render(request, 'ucenter/logistics-settings.html', locals())
    return render(request, 'velonic/logistics-settings.html', locals())



def logistics_message(request,user_id):

    return render(request, 'ucenter/logistics-collect-message.html', locals())


def logistics_collect(request):
    if not request.user.is_authenticated():
        return redirect('signin')
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        msg = "未授权用户"
        return render(request, 'aside/error.html', locals())
    try:
        user_profile = UserProfile.objects.get(user=user)
    except:
        return redirect('profile')
    if request.method == 'POST':
        startDate = request.POST.get('startDate')
        endDate = request.POST.get('endDate')

        selectDate = request.POST.get('selectDate')

        if selectDate == '1':
            d1 = datetime.datetime.now()
            d2 = d1 + datetime.timedelta(days=-1)
            endDate = startDate = d2.strftime('%Y-%m-%d')
        elif selectDate == '2':
            d1 = datetime.datetime.now()
            endDate = startDate = d1.strftime('%Y-%m-%d')
        elif selectDate == '3':
            d1 = datetime.datetime.now()
            d2 = d1 + datetime.timedelta(days=-6)
            endDate = d1.strftime('%Y-%m-%d')
            startDate = d2.strftime('%Y-%m-%d')
        elif selectDate == '4':
            d1 = datetime.datetime.now()
            d2 = d1 + datetime.timedelta(days=-30)
            endDate = d1.strftime('%Y-%m-%d')
            startDate = d2.strftime('%Y-%m-%d')
        elif selectDate == '0':
            endDate = startDate = ''


        #print(startDate)
        #print(endDate)

        selectPageSize = request.POST.get('selectPageSize')

        #print(selectPageSize)

    return render(request, 'ucenter/logistics-collect.html', locals())

def logistics_list(request):
    if not request.user.is_authenticated():
        return redirect('signin')
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        msg = "未授权用户"
        return render(request, 'aside/error.html', locals())
    try:
        user_profile = UserProfile.objects.get(user=user)
    except:
        return redirect('profile')
    wuliu = Logistics.objects.filter(user = user,send_time__gte=now().date() + timedelta(days=-90)).order_by('-send_time')
    #return render(request, 'ucenter/logistics-list.html', locals())
    return render(request, 'velonic/logistics-list.html', locals())



def logistics_import(request):
    if not request.user.is_authenticated():
        return redirect('signin')
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        msg = "未授权用户"
        return render(request, 'aside/error.html', locals())
    if request.method == 'POST':
        file = request.FILES.get('wuliu')
        if file:
            path = 'tmp/'
            path += user.username
            ts = str(int(time.time()))
            path += ts
            path += '.xls'
            open(path, 'wb').write(file.file.read())
            data = xlrd.open_workbook(path)
            table = data.sheets()[0]
            nrows = table.nrows
            cgnum = cfnum = 0
            for i in range(1,nrows):
                #print('%s\t%s\t%s'%(table.row_values(i)[0],table.row_values(i)[1],table.row_values(i)[2]))
                channel = table.row_values(i)[0].strip()
                tracking_number = table.row_values(i)[1].strip()
                dtstr = table.row_values(i)[2].strip()
                send_time = datetime.datetime.strptime(dtstr, "%Y-%m-%d %H:%M:%S")
                destination = table.row_values(i)[3].strip()
                try:
                    ll = Logistics.objects.create(user=user,channel=channel,tracking_number=tracking_number,send_time=send_time,destination=destination)
                    cgnum +=1
                except Exception as e:
                    cfnum +=1
                    #print(e)
            os.remove(path)
            msg = '导入完毕！成功：%d条，失败(重复)：%d条'%(cgnum,cfnum)
            return render(request, 'aside/error.html', locals())
        else:
            msg = '文件错误！'
            return render(request, 'aside/error.html', locals())
    #return render(request, 'ucenter/logistics-import.html', locals())
    return render(request, 'velonic/logistics-import.html', locals())



def logistics_import2(request):
    if not request.user.is_authenticated():
        return redirect('signin')
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        msg = "未授权用户"
        return render(request, 'aside/error.html', locals())
    if request.method == 'POST':
        file = request.FILES.get('wuliu')
        if file:
            path = 'tmp/'
            path += user.username
            ts = str(int(time.time()))
            path += ts
            path += '.xls'
            open(path, 'wb').write(file.file.read())
            data = xlrd.open_workbook(path)
            table = data.sheets()[0]
            nrows = table.nrows
            cgnum = cfnum = 0
            for i in range(1,nrows):
                if table.row_values(i)[2]:
                    track_number = table.row_values(i)[1].strip()
                    weight = str(table.row_values(i)[6])
                    country = table.row_values(i)[5].strip()
                    fee = str(table.row_values(i)[14])
                    try:
                        sql = 'INSERT INTO `m_profit` (`PROFIT_ID`,`TRACKNUM`,`WEIGHT`,`COUNTRY`,`FEE`) VALUES (UUID(),"%s","%s","%s",%s)'%(track_number,weight,country,fee)
                        conn=linkDB()
                        cur=conn.cursor()
                        cur.execute(sql)
                        conn.commit()
                        cgnum +=1
                    except Exception as e:
                        cfnum +=1
                        #print(e)
            os.remove(path)
            msg = '导入完毕！成功：%d条，失败(重复)：%d条'%(cgnum,cfnum)
            return render(request, 'aside/error.html', locals())
        else:
            msg = '文件错误！'
            return render(request, 'aside/error.html', locals())
    return render(request, 'ucenter/logistics-import.html', locals())


def editWordsCategory(request):
    if not request.user.is_authenticated():
        return redirect('signin')
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        msg = "未授权用户"
        return render(request, 'aside/error.html', locals())
    url = request.META.get("HTTP_REFERER")
    if not url:
        url = 'dashboard'

    wid = request.GET.get('wid')
    cid = request.GET.get('cid')
    if wid and cid:
        try:
            word = SearchWords.objects.get(id=wid)
            category = Category.objects.get(id=cid)
            word.category = category
            word.save()
        except Exception as e:
            print(e)
    return redirect(url)

def logistics_mark(request):
    if not request.user.is_authenticated():
        return redirect('signin')
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        msg = "未授权用户"
        return render(request, 'aside/error.html', locals())
    url = request.META.get("HTTP_REFERER")
    if not url:
        url = '/logistics/index'
    try:
        id = request.GET.get('id')
        status = request.GET.get('s')
        days = request.GET.get('d')
        track_number = request.GET.get('t')
        if id:
            logistics = Logistics.objects.get(id=id)
            if logistics.user != user:
                msg = "您无权操作此产品"
                return render(request, 'aside/error.html', locals())
            logistics.mark = True
            logistics.save()
        elif track_number and status and days:
            logistics = Logistics.objects.get(tracking_number=track_number)
            if logistics.user != user:
                msg = "您无权操作此产品"
                return render(request, 'aside/error.html', locals())
            logistics.status = int(status)
            logistics.num1 = int(days)

            logistics.save()


    except Exception as e:
        print(e)
    return redirect(url+'#yichang')



def saveLS(request):
    id = request.GET.get('id')
    num = request.GET.get('num')
    try:
        ls = LogisticsSetting.objects.get(pk=id)
        if int(num)==ls.regulation:
            return HttpResponse(-1)
        else:
            ls.regulation = int(num)
            ls.save()
            return HttpResponse(1)
    except:
        return HttpResponse(0)

@csrf_exempt
def getComplete(request):
    user_id = request.POST.get('user_id')
    user = User.objects.get(id=user_id)
    complete = Logistics.objects.filter(user=user,query_time__startswith=datetime.datetime.now().date()).count()

    return HttpResponse(complete)

def getyichang(request):
    if not request.user.is_authenticated():
        return redirect('signin')
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        msg = "未授权用户"
        return render(request, 'aside/error.html', locals())
    yc = request.GET.get('yc')
    wuliu = Logistics.objects.filter(user = user,mark=False,status=3)
    if yc:
        try:
            wuliu = wuliu.filter(num1__gt=int(yc))
            wuliu = serialize('json',wuliu)
            response = HttpResponse(json.dumps(wuliu),content_type='application/json')
            response.set_cookie('yc_chose',yc)
            return response
        except:
            return 0
    else:
        return 0


def getyujing(request):
    if not request.user.is_authenticated():
        return redirect('signin')
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        msg = "未授权用户"
        return render(request, 'aside/error.html', locals())
    yj = request.GET.get('yj')
    wuliu = Logistics.objects.filter(user = user,mark=False)
    wuliu = wuliu.exclude(status=0)
    wuliu = wuliu.exclude(status=1)
    wuliu = wuliu.exclude(status=2)
    wuliu = wuliu.exclude(status=6)
    wuliu = wuliu.exclude(status=8)
    if yj:
        try:
            wuliu = wuliu.filter(num1__gt=int(yj))
            wuliu = serialize('json',wuliu)
            response = HttpResponse(json.dumps(wuliu),content_type='application/json')
            response.set_cookie('yj_chose',yj)
            return response
        except:
            return 0
    else:
        return 0



def monitor_store(request):
    if not request.user.is_authenticated():
        return redirect('signin')
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        msg = "未授权用户"
        return render(request, 'aside/error.html', locals())

    try:
        user_profile = UserProfile.objects.get(user=user)
    except:
        return redirect('profile')

    storeList = Store.objects.filter(monitor_user=user)

    return render(request, 'velonic/ucenter-store.html',locals())


def getNotice(request):
    data = dict()
    notice = Notice.objects.filter(send_time__gte=now() + timedelta(minutes=-15)).order_by('-send_time')[:3]
    total = notice.count()
    data["notice"] = []
    data["total"] = total
    for n in notice:
        send_time = n.send_time.strftime('%Y-%m-%d %H:%M:%S')
        data["notice"].append({'id': n.id, 'title': n.title, 'send_time': send_time, 'link':n.link})
    return HttpResponse(json.dumps(data),content_type='application/json')


def getMail(request):
    data = dict()
    if not request.user.is_authenticated():
        return redirect('signin')
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        data["total"] = 0
        data["msg"] = u'没有新消息'
        return HttpResponse(json.dumps(data),content_type='application/json')

    messages = Message.objects.filter(send_time__gte=now() + timedelta(days=-1),receiver=user).order_by('-send_time')[:3]
    total = messages.count()
    data["notice"] = []
    data["total"] = total
    for msg in messages:
        send_time = msg.send_time.strftime('%Y-%m-%d %H:%M:%S')
        userinfo = UserProfile.objects.get(user=msg.sender)
        if userinfo.avatar:
            img = '/uploads/%s'%(userinfo.avatar)
        else:
            img = '/static/velonic/img/avatar-2.jpg'
        data["notice"].append({'id': msg.id, 'title': msg.title, 'send_time': send_time,'avatar':img,'sender':msg.sender.username})
    return HttpResponse(json.dumps(data),content_type='application/json')


def getAlibaba(request):
    data = dict()
    link = request.get_full_path()

    link = link.split('url=')

    if len(link)>1:
        url = link[1]
    else:
        url = None
    if url:
        url = urllib.unquote(url)
        result = getGoodsInfo(url)

        if not result.has_key('error'):
            data["title"] = result["title"]
            data["thumb"] = result["thumb"]
            data["company"] = result["company"]


    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')



def mailBox(request):
    if not request.user.is_authenticated():
        return redirect('signin')
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        msg = "未授权用户"
        return render(request, 'aside/error.html', locals())

    messages = Message.objects.filter(receiver=user).order_by('-send_time')

    messages = getPage(request, messages,20)

    return render(request, 'velonic/mail-box.html',locals())


def article(request,id):
    try:
        category_list = NewCategory.objects.all()
        article = Article.objects.get(pk=id)
        return render(request, 'velonic/article-read.html',locals())
    except:
        msg = "没有找到该内容"
        return render(request, 'aside/error.html', locals())


def articleList(request):
    article_list = Article.objects.all()
    c = request.GET.get('c')

    category_list = NewCategory.objects.all()
    if c:
        try:
            c = int(c)
            category = NewCategory.objects.get(pk=c)
            article_list = article_list.filter(category=category)
        except:
            pass

    article_list = getPage(request, article_list,20)
    return render(request, 'velonic/article-box.html',locals())


def readMail(request,id):
    if not request.user.is_authenticated():
        return redirect('signin')
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        msg = "未授权用户"
        return render(request, 'aside/error.html', locals())
    try:
        mail = Message.objects.get(pk=id)
        sender = UserProfile.objects.get(user=mail.sender)
        try:
            MessageMark.objects.create(message=mail,user=user,is_read=True)
        except:
            #print(e)
            pass
        return render(request, 'velonic/mail-read.html',locals())
    except:
        msg = "没有找到该内容"
        return render(request, 'aside/error.html', locals())



def getNoticeAjax(request):
    id = request.GET.get('id')
    if id:
        try:
            data = dict()
            notice = Notice.objects.get(pk=id)
            data['title'] = notice.title
            data['content'] = notice.content
            data['id'] = notice.id
            return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')
        except Exception as e:
            print(e)
    return HttpResponse(0)

def readNotice(request,id):
    if not request.user.is_authenticated():
        return redirect('signin')
    user = request.user
    if user.groups.filter(name='VIP').count() > 0 :
        is_editor = True
        roles = 'VIP用户'
    elif user.groups.filter(name='普通用户').count() > 0 :
        is_reviewed = True
        roles = '普通用户'
    elif user.groups.filter(name='试用用户').count() > 0 :
        is_reviewed = True
        roles = '试用用户'
    else:
        msg = "未授权用户"
        return render(request, 'aside/error.html', locals())
    try:
        notice = Notice.objects.get(pk=id)
        try:
            NoticeMark.objects.create(notice=notice,user=user,is_read=True)
        except:
            #print(e)
            pass
        return render(request, 'velonic/notice-read.html',locals())
    except:
        msg = "没有找到该内容"
        return render(request, 'aside/error.html', locals())



@csrf_exempt
def readNoticeAjax(request):
    id = request.POST.get('id')
    user = request.user
    if id and user:
        try:
            notice = Notice.objects.get(pk=id)
            NoticeMark.objects.create(notice=notice,user=user,is_read=True)
        except:
            pass
    return HttpResponse(0)

def getTransactions(productId,days):
    orderlist = []
    conn=LinkDB()
    cur=conn.cursor()
    sql = 'SELECT COUNT( * ) as "orders" FROM `transaction` WHERE product_id=%s GROUP BY DATE( `pub_date`) ORDER BY DATE( `pub_date`) DESC LIMIT %d '%(productId,days)
    cur.execute(sql)
    for item in cur:
        orderlist.append(item[0])
    cur.close()
    conn.close()
    return orderlist

def updateFirstOrderTime(productId):
    conn = LinkDB()
    cur=conn.cursor()
    sql = 'UPDATE products SET first_order_date=(SELECT date(MIN(pub_date)) FROM `transaction` WHERE product_id=%s) WHERE product_id=%s'%(productId,productId)
    cur.execute(sql)
    conn.commit()

def calcScore(productId):
    orderList = getTransactions(productId,30)

    a = b = 0
    if len(orderList)<30:
        orderList = orderList[:14]
        a = sum(orderList[:7])
        b = sum(orderList[7:])
        if len(orderList)>=6 and len(orderList)<14:
            orderList = orderList[:6]
            a = sum(orderList[:3])
            b = sum(orderList[3:])
    else:
        a = sum(orderList[:15])
        b = sum(orderList[15:])

    if b != 0:
        conn = LinkDB()
        cur=conn.cursor()
        sql = 'UPDATE products SET score=%s WHERE product_id=%s'%(str(round(float(a)/b,2)),productId)
        cur.execute(sql)
        conn.commit()

def getSupplier(request):
    link = request.get_full_path()

    link = link.split('url=')

    if len(link)>1:
        url = link[1]
    else:
        url = None

    data = dict()
    if url:
        url = urllib.unquote(url)
        Supplier.objects.create(url=url)

        time.sleep(3)

        s = Supplier.objects.get(url=url)

        if s.title:
            data["title"] = s.title
            data["thumb"] = s.thumb
            data["company"] = s.seller

        Supplier.objects.get(url=url).delete()

    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')

def getSku(request):

    link = request.get_full_path()

    link = link.split('url=')

    if len(link)>1:
        url = link[1]
    else:
        url = None

    data = dict()
    if url:
        url = url.replace('https','http')
        url = urllib.unquote(url)
        result = getSkuInfo(url)

        if result.has_key('thumb'):
            data = result

    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')


def getPackage(request):
    productId = request.GET.get('productId')
    data = dict()
    if productId:
        p = Products.objects.get(product_id=productId)
        try:
            package = Package.objects.get(product=p)
            data["Length"] = package.length
            data["Width"] = package.width
            data["Height"] = package.height
            data["Weight"] = package.weight
            data["error"] = 0
        except:
            if 'http' in p.product_link:
                productLink = p.product_link
            else:
                productLink = 'http:%s'%(p.product_link)
            package = getPackageDetail(productLink)

            #print(package)

            if package["error"]:
                data["error"] = 1
                data["msg"] = '失败：%s'%(package["msg"])
            else:
                data["Length"] = package["Length"]
                data["Width"] = package["Width"]
                data["Height"] = package["Height"]
                data["Weight"] = package["Weight"]
                data["error"] = 0

                Package.objects.create(product=p,length=package["Length"],width=package["Width"],height=package["Height"],weight=package["Weight"],update_date=datetime.date.today())

    else:
        data["error"] = 1
        data["msg"] = '产品ID有误！'

    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')


def ajaxSearch(request):
    data = dict()
    q = request.GET.get('q','')

    sql = 'SELECT `id`,`product_id`,`product_title`,`thumb`,`currency`,`low_price`,`orders`,`votes`,`average_star_rating`,`wish_list` FROM `products` '
    if q != '':
        sql += ' WHERE `product_title` like "%'+q.replace('"','\\"')+'%" '
        sql += ' OR `product_title_chinese` like "%'+q.replace('"','\\"')+'%" '
        sql += ' OR `product_id` like "%'+q.replace('"','\\"')+'%" '

    sql += ' LIMIT 30'

    pData = my_custom_sql(sql)
    data["total_count"] = len(pData)
    data["items"] = pData


    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')

def ajaxCountry(request):
    data = dict()
    q = request.GET.get('q','')
    t = request.GET.get('t','0')

    q  = str(q).upper()

    if t == '1':
        sql = 'SELECT `country_code` as `id`,`country_name`,`country_name_cn`,`country_code` FROM `country` '
    else:
        sql = 'SELECT `country_name_cn` as `id`,`country_name`,`country_name_cn`,`country_code` FROM `country` '

    if q != '':
        sql += ' WHERE `country_code` like "%'+q.replace('"','\\"')+'%" '
        sql += ' OR `country_name_cn` like "%'+q.replace('"','\\"')+'%" '
        sql += ' OR `country_name` like "%'+q.replace('"','\\"')+'%" '
        sql += ' OR `search_string` like "%'+q.lower().replace('"','\\"')+'%" '

    sql += ' LIMIT 30'

    cData = my_custom_sql(sql)
    data["total_count"] = len(cData)
    data["items"] = cData


    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')

def ajaxCountryForCode(request):
    data = dict()
    q = request.GET.get('q','')
    t = request.GET.get('t','0')

    q  = str(q).upper()

    if t == '1':
        sql = 'SELECT `country_name_cn` as `id`,`country_name`,`country_name_cn`,`country_code` FROM `country` '
    else:
        sql = 'SELECT `country_code` as `id`,`country_name`,`country_name_cn`,`country_code` FROM `country` '
    if q != '':
        sql += ' WHERE `country_code` like "%'+q.replace('"','\\"')+'%" '
        sql += ' OR `country_name_cn` like "%'+q.replace('"','\\"')+'%" '
        sql += ' OR `country_name` like "%'+q.replace('"','\\"')+'%" '
        sql += ' OR `search_string` like "%'+q.lower().replace('"','\\"')+'%" '

    sql += ' LIMIT 30'

    cData = my_custom_sql(sql)
    data["total_count"] = len(cData)
    data["items"] = cData


    return HttpResponse(json.dumps(data,ensure_ascii=False),content_type='application/json; charset=utf-8')